package org.kjtc.exportService;

import java.io.FileOutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import javax.servlet.ServletOutputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFFooter;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.kjtc.balanceEntity.ChargingStation;

@SuppressWarnings({ "deprecation", "unused" })
public class ExportCostBalanceBill<T> {

	@SuppressWarnings({ "rawtypes", "unchecked" })
	public void exportExcel(String title, String[] headers, Collection<T> dataset, ServletOutputStream servletOutputStream, double pay, double kwh, double ech, double diff) throws Exception { 
		// 声明一个工作薄  
        HSSFWorkbook workbook = new HSSFWorkbook();  
        // 生成一个表格  
        HSSFSheet sheet = workbook.createSheet(title);
        // 纸张大小、方向
        HSSFPrintSetup ps = sheet.getPrintSetup();    
        ps.setLandscape(false); // 打印方向，true：横向，false：纵向(默认)     
        ps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); //纸张类型 
        
        HSSFFooter footer = sheet.getFooter();
        footer.setCenter(HSSFFooter.page() + "/ " + HSSFFooter.numPages() + "页");
        
        // 生成一个样式  
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式  
        style.setFillForegroundColor(HSSFColor.WHITE.index);  
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);  
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置自动换行
        style.setWrapText(true);
        // 设置文字水平居中
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置文字垂直居中
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 生成一个字体  
        HSSFFont font = workbook.createFont();  
        font.setColor(HSSFColor.AUTOMATIC.index);
        font.setFontName("Arial");
        font.setFontHeightInPoints((short) 10);
        // 加粗
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式  
        style.setFont(font);
        
        // 生成一个样式  
        HSSFCellStyle style_heji = workbook.createCellStyle();
        // 设置这些样式  
        style_heji.setFillForegroundColor(HSSFColor.WHITE.index);  
        style_heji.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
        style_heji.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
        style_heji.setBorderLeft(HSSFCellStyle.BORDER_NONE);  
        style_heji.setBorderRight(HSSFCellStyle.BORDER_NONE);  
        style_heji.setBorderTop(HSSFCellStyle.BORDER_NONE);
        // 设置自动换行
        style_heji.setWrapText(true);
        // 设置文字水平居中
        style_heji.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        // 设置文字垂直居中
        style_heji.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 生成一个字体  
        HSSFFont font_heji = workbook.createFont();  
        font_heji.setColor(HSSFColor.AUTOMATIC.index);
        font_heji.setFontName("Arial");
        font_heji.setFontHeightInPoints((short) 10);
        // 加粗
        font_heji.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式  
        style_heji.setFont(font_heji);
        
        workbook.setRepeatingRowsAndColumns(0, 0, headers.length - 1, 0, 2);
        
        sheet.setColumnWidth(0, 1400);
        sheet.setColumnWidth(1, 4800);
        sheet.setColumnWidth(2, 3600);
        sheet.setColumnWidth(3, 3600);
        sheet.setColumnWidth(4, 3200);
        sheet.setColumnWidth(5, 3200);
        sheet.setColumnWidth(6, 3200);
        sheet.setColumnWidth(7, 3200);
        sheet.setColumnWidth(8, 3200);
        sheet.setColumnWidth(9, 3200);
        sheet.setColumnWidth(10, 3200);
        sheet.setColumnWidth(11, 3200);
        sheet.setColumnWidth(12, 3200);
        sheet.setColumnWidth(13, 3200);
        sheet.setColumnWidth(14, 3200);
        sheet.setColumnWidth(15, 3200);
        sheet.setColumnWidth(16, 3200);
        sheet.setColumnWidth(17, 3200);
        sheet.setColumnWidth(18, 3200);
        sheet.setColumnWidth(19, 3200);
        sheet.setColumnWidth(20, 3200);
        sheet.setColumnWidth(21, 3200);
        sheet.setColumnWidth(22, 3200);
        sheet.setColumnWidth(23, 3200);
        sheet.setColumnWidth(24, 3200);
        sheet.setColumnWidth(25, 3200);
        sheet.setColumnWidth(26, 4000);
        sheet.setColumnWidth(27, 4000);
        sheet.setColumnWidth(28, 3200);
        sheet.setColumnWidth(29, 4000);
        sheet.setColumnWidth(30, 4000);
        sheet.setColumnWidth(31, 3200);
        // 生成一个样式  
        HSSFCellStyle style_data = workbook.createCellStyle();
        // 设置这些样式  
        style_data.setFillForegroundColor(HSSFColor.WHITE.index);  
        style_data.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
        style_data.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
        style_data.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
        style_data.setBorderRight(HSSFCellStyle.BORDER_THIN);  
        style_data.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置自动换行
        style_data.setWrapText(true);
        // 设置文字水平居中
        style_data.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置文字垂直居中
        style_data.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 生成一个字体  
        HSSFFont font_data = workbook.createFont();  
        font_data.setColor(HSSFColor.AUTOMATIC.index);
        font_data.setFontName("Arial");
        font_data.setFontHeightInPoints((short) 10);
        // 不加粗
        font_data.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        // 把字体应用到当前的样式  
        style_data.setFont(font_data);
        
        // 生成一个样式  
        HSSFCellStyle style_title = workbook.createCellStyle();
        // 设置这些样式  
        style_title.setFillForegroundColor(HSSFColor.WHITE.index);  
        style_title.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
//        style_title.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
//        style_title.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
//        style_title.setBorderRight(HSSFCellStyle.BORDER_THIN);  
//        style_title.setBorderTop(HSSFCellStyle.BORDER_THIN);  
        style_title.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style_title.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 生成一个字体  
        HSSFFont font_title = workbook.createFont();  
        font_title.setColor(HSSFColor.AUTOMATIC.index);  
        font_title.setFontHeightInPoints((short) 26);
        font_title.setFontName("Arial");
        // 加粗
        font_title.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式  
        style_title.setFont(font_title);
        
        // 产生title  
        HSSFRow row_title = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
        	HSSFCell cell = row_title.createCell(i);  
            cell.setCellStyle(style_title);
            cell.getCellStyle().setBorderBottom(HSSFCellStyle.BORDER_NONE);
            cell.getCellStyle().setBorderTop(HSSFCellStyle.BORDER_NONE);
            cell.getCellStyle().setBorderLeft(HSSFCellStyle.BORDER_NONE);
            cell.getCellStyle().setBorderRight(HSSFCellStyle.BORDER_NONE);
		}
        Cell cell_title = row_title.createCell(0);
        cell_title.setCellStyle(style_title);
        cell_title.setCellValue(title);
        // 合并单元格
        CellRangeAddress cra_title = new CellRangeAddress(0, 0, 0, headers.length - 1);
        //在sheet里增加合并单元格  
        sheet.addMergedRegion(cra_title);
        
        // 产生标题行
        HSSFRow row_header = sheet.createRow(1);
        for (short i = 0; i < headers.length; i++) { 
            HSSFCell cell = row_header.createCell(i);  
            cell.setCellStyle(style);  
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);  
            cell.setCellValue(text);
        }
        
        
        HSSFCellStyle cellStyle1 = workbook.createCellStyle();

		 HSSFDataFormat format= workbook.createDataFormat();

        cellStyle1.setDataFormat(format.getFormat("yyyy-MM-dd hh:mm:ss"));
     // 设置这些样式  
        cellStyle1.setFillForegroundColor(HSSFColor.WHITE.index);  
        cellStyle1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
        cellStyle1.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
        cellStyle1.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
        cellStyle1.setBorderRight(HSSFCellStyle.BORDER_THIN);  
        cellStyle1.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置自动换行
        cellStyle1.setWrapText(true);
        // 设置文字水平居中
        cellStyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置文字垂直居中
        cellStyle1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        cellStyle1.setFont(font_data);
        
        HSSFCellStyle cellStyle = workbook.createCellStyle();  
//        cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
        // 设置这些样式  
        cellStyle.setFillForegroundColor(HSSFColor.WHITE.index);  
        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);  
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置自动换行
        cellStyle.setWrapText(true);
        // 设置文字水平居中
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置文字垂直居中
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        cellStyle.setFont(font_data);
//        sheet.createFreezePane(0, 3, 0, 3);
        // 遍历集合数据，产生数据行  
        Iterator<T> it = dataset.iterator();  
        int index = 1;
        String chargingStationName = "";
        String lastStationNameString = "";
        int count = 1;
        int rowNo = 1;
        while (it.hasNext()){
        	index++;
        	HSSFRow row = sheet.createRow(index);
        	HSSFCell cell_No = row.createCell(0);
        	cell_No.setCellStyle(style_data);
        	cell_No.setCellValue(rowNo);
        	T t = (T) it.next();
        	// 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值  
            Field[] fields = t.getClass().getDeclaredFields();
        	int cellIndex = 1;
        	boolean flag = false;
            for (int i = 0; i < fields.length; i++) {
            	String[] notColumn = {"getBalanceBillID", "getBalanceBillName", "getBalanceMonth", "getTotalPayElectricCharge", "getSumTotalChargingKWH", "getTotalCostSubtotal", "getTotalDifference", "getChargingFixedPrice", "getChargingSharpPrice", "getChargingPeakPrice", "getChargingFlatPrice", "getChargingValleyPrice", "getCreateUser", "getCreateDTTM", "getUpdateUser", "getUpdateDTTM"};
            	String[] colspanColumn = {"getCheckMeterLastTime", "getCheckMeterThisTime", "getTotalActiveKWH", "getSharpActiveKWH", "getPeakActiveKWH", "getFlatActiveKWH", "getValleyActiveKWH", "getSharpProportion", "getPeakProportion", "getFlatProportion", "getValleyProportion", "getPayElectricCharge", "getTotalChargingKWH", "getStationRatio", "getCostSubtotal", "getDifference", "getRatio"};
            	Field field = fields[i];  
                String fieldName = field.getName();  
                String getMethodName = "get"  + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                if ("getBalanceBillID".equals(getMethodName)) {
					break;
				} else if (Arrays.asList(notColumn).contains(getMethodName)) {
					continue;
				} else if ("getChargingStationName".equals(getMethodName)) {
					Class tCls = t.getClass();  
	                Method getMethod = tCls.getMethod(getMethodName, new Class[]{});  
	                Object value = getMethod.invoke(t, new Object[]{});
	                if ("".equals(chargingStationName)) {
	                	chargingStationName = value.toString();
					} else {
						if (!chargingStationName.equals(value.toString())) {
							chargingStationName = value.toString();
							rowNo++;
							flag = true;
							cell_No.setCellValue(rowNo);
						} else {
							count++;
						}
					}
	                HSSFCell cell = row.createCell(cellIndex);
	            	cellIndex++;
	            	cell.setCellStyle(style_data);
	            	cell.setCellValue(value.toString());
				} else {
					HSSFCell cell = row.createCell(cellIndex);
	            	cellIndex++;
	            	cell.setCellStyle(style_data);  
					Class tCls = t.getClass();  
	                Method getMethod = tCls.getMethod(getMethodName, new Class[]{});  
	                Object value = getMethod.invoke(t, new Object[]{});
	                // 单元格赋值
					if(lastStationNameString == chargingStationName && Arrays.asList(colspanColumn).contains(getMethodName)) {
						setValue("", cell, workbook, getMethodName, cellStyle, font_data, cellStyle1);
					} else {
		                setValue(value, cell, workbook, getMethodName, cellStyle, font_data, cellStyle1);
					}
				}
                if (index == dataset.size() + 2) {
					flag = true;
				}
			}
            lastStationNameString = chargingStationName;
            if (flag) {
            	if (index == dataset.size() + 2) {
                	sheet.addMergedRegion(new CellRangeAddress(index - count + 1, index, 0, 0));
                    sheet.addMergedRegion(new CellRangeAddress(index - count + 1, index, 1, 1));
                    sheet.addMergedRegion(new CellRangeAddress(index - count + 1, index, 2, 2));
                    sheet.addMergedRegion(new CellRangeAddress(index - count + 1, index, 3, 3));
                    sheet.addMergedRegion(new CellRangeAddress(index - count + 1, index, 18, 18));
                    sheet.addMergedRegion(new CellRangeAddress(index - count + 1, index, 19, 19));
                    sheet.addMergedRegion(new CellRangeAddress(index - count + 1, index, 20, 20));
                    sheet.addMergedRegion(new CellRangeAddress(index - count + 1, index, 21, 21));
                    sheet.addMergedRegion(new CellRangeAddress(index - count + 1, index, 22, 22));
                    sheet.addMergedRegion(new CellRangeAddress(index - count + 1, index, 23, 23));
                    sheet.addMergedRegion(new CellRangeAddress(index - count + 1, index, 24, 24));
                    sheet.addMergedRegion(new CellRangeAddress(index - count + 1, index, 25, 25));
                    sheet.addMergedRegion(new CellRangeAddress(index - count + 1, index, 26, 26));
                    sheet.addMergedRegion(new CellRangeAddress(index - count + 1, index, 27, 27));
                    sheet.addMergedRegion(new CellRangeAddress(index - count + 1, index, 28, 28));
                    sheet.addMergedRegion(new CellRangeAddress(index - count + 1, index, 29, 29));
                    sheet.addMergedRegion(new CellRangeAddress(index - count + 1, index, 30, 30));
                    sheet.addMergedRegion(new CellRangeAddress(index - count + 1, index, 31, 31));
                    sheet.addMergedRegion(new CellRangeAddress(index - count + 1, index, 32, 32));
				} else {
                	sheet.addMergedRegion(new CellRangeAddress(index - count, index - 1, 0, 0));
                    sheet.addMergedRegion(new CellRangeAddress(index - count, index - 1, 1, 1));
                    sheet.addMergedRegion(new CellRangeAddress(index - count, index - 1, 2, 2));
                    sheet.addMergedRegion(new CellRangeAddress(index - count, index - 1, 3, 3));
                    sheet.addMergedRegion(new CellRangeAddress(index - count, index - 1, 18, 18));
                    sheet.addMergedRegion(new CellRangeAddress(index - count, index - 1, 19, 19));
                    sheet.addMergedRegion(new CellRangeAddress(index - count, index - 1, 20, 20));
                    sheet.addMergedRegion(new CellRangeAddress(index - count, index - 1, 21, 21));
                    sheet.addMergedRegion(new CellRangeAddress(index - count, index - 1, 22, 22));
                    sheet.addMergedRegion(new CellRangeAddress(index - count, index - 1, 23, 23));
                    sheet.addMergedRegion(new CellRangeAddress(index - count, index - 1, 24, 24));
                    sheet.addMergedRegion(new CellRangeAddress(index - count, index - 1, 25, 25));
                    sheet.addMergedRegion(new CellRangeAddress(index - count, index - 1, 26, 26));
                    sheet.addMergedRegion(new CellRangeAddress(index - count, index - 1, 27, 27));
                    sheet.addMergedRegion(new CellRangeAddress(index - count, index - 1, 28, 28));
                    sheet.addMergedRegion(new CellRangeAddress(index - count, index - 1, 29, 29));
                    sheet.addMergedRegion(new CellRangeAddress(index - count, index - 1, 30, 30));
                    sheet.addMergedRegion(new CellRangeAddress(index - count, index - 1, 31, 31));
                    sheet.addMergedRegion(new CellRangeAddress(index - count, index - 1, 32, 32));
				}
                count = 1;
			}
		}
        HSSFRow rowTotal = sheet.createRow(index + 1);
    	HSSFCell cell_NoTotal = null;
    	cell_NoTotal = rowTotal.createCell(0);
    	cell_NoTotal.setCellStyle(style_data);
    	cell_NoTotal.setCellValue(rowNo + 1);
    	
    	cell_NoTotal = rowTotal.createCell(1);
    	cell_NoTotal.setCellStyle(style_data);
    	cell_NoTotal.setCellValue("合 计");
    	
    	for(int i = 0; i < 25; i++){
    		cell_NoTotal = rowTotal.createCell(2 + i);
        	cell_NoTotal.setCellStyle(style_data);
        	cell_NoTotal.setCellValue("-");
    	}
    	cell_NoTotal = rowTotal.createCell(27);
    	cell_NoTotal.setCellStyle(style_data);
    	cell_NoTotal.setCellValue(pay);
    	
    	cell_NoTotal = rowTotal.createCell(28);
    	cell_NoTotal.setCellStyle(style_data);
    	cell_NoTotal.setCellValue(kwh);
    	
    	cell_NoTotal = rowTotal.createCell(29);
    	cell_NoTotal.setCellStyle(style_data);
    	cell_NoTotal.setCellValue("-");
    	
    	cell_NoTotal = rowTotal.createCell(30);
    	cell_NoTotal.setCellStyle(style_data);
    	cell_NoTotal.setCellValue(ech);
    	
    	cell_NoTotal = rowTotal.createCell(31);
    	cell_NoTotal.setCellStyle(style_data);
    	cell_NoTotal.setCellValue(diff);
    	
    	cell_NoTotal = rowTotal.createCell(32);
    	cell_NoTotal.setCellStyle(style_data);
    	cell_NoTotal.setCellValue("-");
    	
        workbook.write(servletOutputStream);
        servletOutputStream.flush();
        servletOutputStream.close();
        
    }

	private void setValue(Object value, HSSFCell cell, HSSFWorkbook workbook, String getMethodName, HSSFCellStyle cellStyle, HSSFFont font_data, HSSFCellStyle cellStyle1) {
		if (value instanceof Integer) {
			int intValue = (Integer) value;
			cell.setCellValue(intValue);
		} else if (value instanceof Double) {
			if (!"getBalanceRatio".equals(getMethodName) && !"getChargingUnitPrice".equals(getMethodName) && !"getOilPrice".equals(getMethodName) && !"getServiceUnitPrice".equals(getMethodName)) {
				double dValue = (Double) value;
				cell.setCellValue(dValue);
	            cell.setCellStyle(cellStyle);
			} else {
				double dValue = (Double) value;
				cell.setCellValue(dValue);
			}
		} else if (value instanceof String) {
			String strValue = value.toString();
			cell.setCellValue(strValue);
		} else if (value instanceof Date) {
			Date date = (Date) value;
			
			cell.setCellValue(date);
			cell.setCellStyle(cellStyle1);
		}
	}
	
    public static int stringNumbers(String str, String des) {
    	int counter = 0;
    	while (str.indexOf(des) != -1) {
    		counter++;
    		str = str.substring(str.indexOf(des) + des.length());
    	}
        return counter;
    }
}
